﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class AccountManager:ConnectionManager
    {
        #region Adding SalesMan/Buyer

        public SqlException AddAccount(AccountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO ACCOUNTS (AcctCode, EmpID, AcctName, AccountType) VALUES (@Code, @EmpID, @AccountName, @AccountType)";

                oCommand.Parameters.Add(new SqlParameter("@Code", oUnit.Code));
                oCommand.Parameters.Add(new SqlParameter("@EmpID", oUnit.EmpID));
                oCommand.Parameters.Add(new SqlParameter("@AccountName", oUnit.AccountName));
                oCommand.Parameters.Add(new SqlParameter("@AccountType", oUnit.AccountType));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }


        public SqlException UpdateAccount(AccountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE ACCOUNTS SET EmpID=@EmpID, AcctName=@AcctName, AccountType=@AccountType WHERE AcctCode=@AcctCode";

                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.Code));
                oCommand.Parameters.Add(new SqlParameter("@EmpID", oUnit.EmpID));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AccountName));
                oCommand.Parameters.Add(new SqlParameter("@AccountType", oUnit.AccountType));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public bool IsAccountExists(string sCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ACCOUNTS WHERE Code=@Code";
                oCommand.Parameters.Add(new SqlParameter("@Code", sCode));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }

        }

        public bool IsAccountNameExists(string sName)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ACCOUNTS WHERE AcctName=@NameOfPerson";
                oCommand.Parameters.Add(new SqlParameter("@NameOfPerson", sName));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }

        }

        #endregion

        public DataTable GetAccounts(string sAccountType)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT EmpID, AcctCode, AcctName, AcctCode + ' - ' + AcctName AccountHolder, AccountType FROM ACCOUNTS WHERE AccountType=@AcctType";
                oCommand.Parameters.Add(new SqlParameter("@AcctType", sAccountType));
                
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }

        }

        public DataTable BrowseAgentsByCode(string sAgentCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                sAgentCode = '%' + sAgentCode.Trim() + '%';

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ACCOUNTS WHERE AcctCode LIKE AcctCode";
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", sAgentCode));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }

        }

        public DataTable GetAgentsByCode(string sAgentCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ACCOUNTS WHERE AcctCode=@AcctCode";
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", sAgentCode));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }

        }

        public DataTable BrowseAgentsByName(string sAgentName)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                sAgentName = '%' + sAgentName.Trim() + '%';

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ACCOUNTS WHERE AcctName LIKE AcctName";
                oCommand.Parameters.Add(new SqlParameter("@AcctName", sAgentName));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }

        }

}}
